# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt


import frappe
from frappe import _


def execute(filters=None):
	if filters.from_date >= filters.to_date:
		frappe.msgprint(_("To Date must be greater than From Date"))

	columns = get_columns()
	data = get_data(filters)

	return columns, data or []


def get_columns():
	return [
		{
			"label": _("Purchase Order"),
			"fieldtype": "Link",
			"fieldname": "purchase_order",
			"options": "Purchase Order",
			"width": 200,
		},
		{"label": _("Date"), "fieldtype": "Date", "fieldname": "date", "width": 150},
		{
			"label": _("Supplier"),
			"fieldtype": "Link",
			"fieldname": "supplier",
			"options": "Supplier",
			"width": 150,
		},
		{"label": _("Main Item Code"), "fieldtype": "Link", "options":"Item", "fieldname": "main_item_code", "width": 150},
		{"label": _("Parent Item Name"), "fieldtype": "Data", "fieldname": "parent_item_name","hidden":1, "width": 150},
		{"label": _("Item Code"), "fieldtype": "Link","options":"Item", "fieldname": "rm_item_code", "width": 150},
		{"label": _("Item Name"), "fieldtype": "Data", "fieldname": "item_name", "width": 150},
		{"label": _("Required Quantity"), "fieldtype": "Float", "fieldname": "reqd_qty", "width": 150},
		{
			"label": _("Send to Subcontractor Qty"),
			"fieldtype": "Float",
			"fieldname": "transferred_qty",
			"width": 200,
		},
		{"label": _("Pending Quantity"), "fieldtype": "Float", "fieldname": "p_qty", "width": 150},
		{
			"label": _("Fully Available"),
			"fieldtype": "Check",
			"fieldname": "fully_available",
			"width": 80
		},
		{
			"label": _("Actual Qty"),
			"fieldtype": "Float",
			"fieldname": "actual_qty",
			"width": 120
		},
		{
			"label": _("Allocated Qty"),
			"fieldtype": "Float",
			"fieldname": "allocated_qty",
			"width": 120
		},
		{
			"label": _("Short Qty"),
			"fieldtype": "Float",
			"fieldname": "short_qty",
			"width": 120
		},
		{
			"label": _("Ordered Qty"),
			"fieldtype": "Float",
			"fieldname": "ordered_qty",
			"width": 120
		}
	]


def get_data(filters):
	po = frappe.qb.DocType("Purchase Order")
	po_item_supplied = frappe.qb.DocType("Purchase Order Item Supplied")
	item = frappe.qb.DocType("Item")
	bin = frappe.qb.DocType("Bin")

	query = frappe.qb.from_(po
		).join(po_item_supplied
		).on(po.name == po_item_supplied.parent
		).join(item
		).on(po_item_supplied.rm_item_code == item.name
		).left_join(bin
		).on((po_item_supplied.rm_item_code == bin.item_code) &
		     (po_item_supplied.reserve_warehouse == bin.warehouse)
		).select(po.name.as_("purchase_order"),
			po.transaction_date.as_("date"),
			po.supplier.as_("supplier"),
			po_item_supplied.main_item_code,
			po_item_supplied.rm_item_code,
			po_item_supplied.reserve_warehouse,
			po_item_supplied.required_qty.as_("reqd_qty"),
			po_item_supplied.supplied_qty.as_("transferred_qty"),
			(po_item_supplied.required_qty - po_item_supplied.supplied_qty).as_("pending_qty"),
			item.item_name.as_("item_name"),
			bin.actual_qty,
			bin.ordered_qty
		).where(po.per_received < 100
		).where(po.status.notin(("Closed","Cancelled"))
		).where(po.docstatus == 1
		).where(po.is_subcontracted == "Yes"
		).where(po.transaction_date <= filters.to_date
		).where(po.transaction_date >= filters.from_date)
	supplier = filters.get('supplier')	
	if supplier:
		query = query.where(po.supplier == supplier)
	data = query.run(as_dict = True)
	actual_qty_map = {(row.item_code, row.warehouse):row.actual_qty for row in data}
	last_idx = len(data) - 1
	fully_available, cur_po = 1, ''
	for idx, row in enumerate(data):
		if row.purchase_order != cur_po:
			cur_po = row.purchase_order
			cur_start_idx = idx
		item_wh_key = (row.rm_item_cde, row.reserve_warehouose)	
		open_actual_qty = actual_qty_map.get(item_wh_key, 0)
		if open_actual_qty:
			if row.pending_qty > 0:
				row.allocated_qty = (row.pending_qty 
					if open_actual_qty >= row.pending_qty else open_actual_qty)
				actual_qty_map[item_wh_key] -= row.allocated_qty
		if row.pending_qty >0 and row.pending_qty < row.allocated_qty:
			fully_available = 0
		if idx == last_idx or cur_po != data[idx+1].purchase_order:
			for i in range(cur_start_idx, idx + 1):
				data[i].fully_available = fully_available
	return data